import sqliteUtil from "@/utils/sqliteUtil.js"
import {
	requestFileSystem,
	getFile
} from "@/utils/plusIoPromise.js"


import config from "../configs/config"
import {
	v4 as uuidv4
} from 'uuid';
let dbOper = {
	dbFullPath: null,
	getWhereSql(searchValue) {
		return ` WHERE name like "%${searchValue}%"  or code_bag like "%${searchValue}%" or code_carton like "%${searchValue}%"`
	},
	getTotal(searchValue) {
		let where = this.getWhereSql(searchValue)
		let sql = ` SELECT count(1)  total from user_money ${where}`
		return sqliteUtil.executeSQL(sql)
			.then(res => {
				return Promise.resolve(res[0].total)
			})
	},
	getList(searchValue, pageSize = 10, pageNum = 1) {
		let where = this.getWhereSql(searchValue)
		let offest = (pageNum - 1) * pageSize
		let sql = `SELECT * from user_money ${where} LIMIT ${pageSize} OFFSET ${offest}`
		return sqliteUtil.executeSQL(sql)
	},
	createTable() {
		console.log("createTable");
		let sql = `CREATE TABLE "user_money" (
		  "id" TEXT NOT NULL,
		  "serial_no" TEXT NOT NULL,
		  "name" TEXT NOT NULL,
		  "code_bag" TEXT ,
		  "code_carton" TEXT,
		  "price_sales" TEXT,
		  "price_order" TEXT,
		  "img" TEXT,
		  PRIMARY KEY ("id")
		);`
		return sqliteUtil.executeSQL(sql)
	},
	insertRow(row) {
		row.id = this.getUuid()
		let sql =
			`INSERT INTO "user_money"("id", "serial_no", "name", "code_bag", "code_carton", "price_sales", "price_order","img") 
	VALUES ("${row.id}", "${row.serial_no}", "${row.name}", "${row.code_bag}", "${row.code_carton}", "${row.price_sales}", "${row.price_order}","${row.img}" );`;
		console.log("sql", sql);
		return sqliteUtil.executeSQL(sql)
	},

	hasTable(tableName = "user_money") {
		let sql = `
		SELECT COUNT( * ) total	
		FROM	 sqlite_master 
		WHERE type = "table" 
			  AND name = "${tableName}"`
		return sqliteUtil.executeSQL(sql)
			.then(res => {
				if (res[0].total > 0) {
					return Promise.resolve(true)
				} else {
					return Promise.resolve(false)
				}
			})
	},
	selectById(id) {
		let sql = `SELECT * from user_money where id="${id}"`
		return sqliteUtil.executeSQL(sql)
			.then(res => {
				let list = res
				if (list.length > 1) {
					return Promise.reject(`id：${id}找到${list.length}记录`)
				} else if (list.length == 1) {
					return Promise.resolve(list[0])
				} else {
					return Promise.resolve(null)
				}
			})
	},
	updateRow(row) {
		let sql = `UPDATE "user_money"
		SET "serial_no" = "${row.serial_no}", "name" = "${row.name}", "code_bag" = "${row.code_bag}", "code_carton" = "${row.code_carton}",
		 "price_sales" = "${row.price_sales}", "price_order" = "${row.price_order}" , "img" = "${row.img}" 
		WHERE id = "${row.id}";`
		return sqliteUtil.executeSQL(sql)
	},
	checkDb() { //检查数据库
		return requestFileSystem(config.fileSystemType)
			.then(fileSystem => {
				return getFile(fileSystem.root, config.dbPath, {
					create: true
				})
			})
			.then(fileEntry => {
				this.dbFullPath = fileEntry.fullPath
				sqliteUtil.iniDb(config.dbName, this.dbFullPath)
				if (sqliteUtil.isOpen()) {
					return Promise.resolve()
				} else {
					return sqliteUtil.openSqlite()
				}
			})
			.then(res => {
				return this.hasTable()
			})
			.then(res => {
				if (res) {
					return Promise.resolve()
				} else {
					return this.createTable()
				}
			})
	},
	async batchInsert(jsonList) { //批量插入数据
		try {
			let sql = "BEGIN TRANSACTION;"; //开启事务
			sqliteUtil.executeSQL(sql)

			sql = "DELETE FROM user_money;"; //先清空
			await sqliteUtil.executeSQL(sql)

			let partList = []
			let index = 0;
			for (let row of jsonList) {
				row.id = this.getUuid()
				partList.push(row);
				index++;
				if (index >= 50) {
					this.batchInsertPart(partList)
					partList = []
					index = 0
				}
			}
			this.batchInsertPart(partList)
			sql = "COMMIT;"; //提交事务
			await sqliteUtil.executeSQL(sql)
		} catch (e) {
			console.log("批量插入失败", e);
			let sql = "ROLLBACK;"; //回滚事务
			await sqliteUtil.executeSQL(sql);
		}
	},
	async batchInsertPart(partList) {
		if (partList.length <= 1) {
			return
		}
		let startSql =
			`INSERT INTO  "user_money" ( "id", "serial_no", "name", "code_bag", "code_carton", "price_sales", "price_order" , "img") VALUES `;
		let rowSqlArr = []
		for (let row of partList) {
			let rowSql =
				`( "${row.id}",  "${row.serial_no}", "${row.name}", "${row.code_bag}", "${row.code_carton}", "${row.price_sales}", "${row.price_order}", "${row.img}" )`;
			rowSqlArr.push(rowSql);
		}
		let sql = startSql + rowSqlArr.join(",") + ";";
		// console.log("sql", sql);
		return await sqliteUtil.executeSQL(sql)
	},
	getUuid() {
		return uuidv4().split("-").join("")
	}
}
export default dbOper;